package com.control;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Fault;

public class FaultControl {

	
	/**
	 * 插入故障信息
	 * @param fault
	 * @return
	 * @throws SQLException
	 */
	public boolean insertFault(Fault fault) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call insertFault('"+fault.getStu_id()+"','"+fault.getFault_des()+"','"+fault.getFault_type()+"')}";
		/*String sql = "Insert into fault(Stu_id, fault_des, fault_time, fault_deal, fault_type) " +
		*		"values("+ fault.getStu_id() +", '"+fault.getFault_des() +"', getDate(), 'false', '"+ fault.getFault_type() +"')";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} finally {
			dbc.close();
		}
	}

	/**
	 * 删除故障
	 * @param fault_id
	 * @return
	 */
	public boolean deleteFault(int fault_id) {
		DBConnect dbc = new DBConnect();
		/*
		String sql1 = "Delete from serve where fault_id = "+ id +"";
		String sql2 = "Delete from fault where fault_id = "+ id +"";
		*/
		String sql = "{call deleteFault("+ fault_id +")}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("删除fault失败！");
			return false;
		} finally {
			dbc.close();	
		}
	}

	/**
	 * 更新故障
	 * @param fault
	 * @return
	 * @throws SQLException
	 */
	public boolean updateFault(Fault fault) throws SQLException {
		DBConnect dbc = new DBConnect();
		String sql="{call updateFault("+fault.getStu_id()+",'"+fault.getFault_time()+"'," +
				"'"+fault.getFault_des()+"','"+fault.getFault_type()+"',"+fault.getFault_id()+")}";
		/*String sql = "update fault set stu_id = '" + fault.getStu_id() + "', " +
		*		"fault_time = getDate(), fault_des = '"+ fault.getFault_des() +"' " + 
		*		"fault_type = '"+ fault.getFault_type() + "'" + 
		*		"where fault_id = '"+ fault.getFault_id() + "' ";
		*/
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql); 
			stmt.execute();
			return true;
		} finally {
			dbc.close();
		}
	}

	/**
	 * 根据故障id获取故障信息
	 * @param fault_id
	 * @return
	 */
	public Fault getFault(int fault_id) {
		DBConnect dbc = new DBConnect();
		Fault fault = new Fault();
		/*
		String sql = "select * from fault,student,dormitory where " + 
				"fault.stu_id = student.stu_id and student.stu_dorm = dormitory.dorm_id and " +
				"fault_id = "+ fault_id +"";
		*/
		String sql = "{call getFault("+ fault_id +")}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			ResultSet rs = stmt.executeQuery();
			if(rs.next()) {
				fault.setDorm_name(rs.getString("dorm_name"));
				fault.setFault_id(rs.getInt("fault_id"));
				fault.setStu_name(rs.getString("stu_name"));
				fault.setFault_des(rs.getString("fault_des"));
				fault.setFault_deal(rs.getBoolean("fault_deal"));
				String str = rs.getString("fault_time");
				if(str != null){
					fault.setFault_time(str.substring(0, 16));
				}
				fault.setFault_type(rs.getString("fault_type"));
				fault.setStu_tel(rs.getString("stu_tel"));
				fault.setFault_reply("无");
				if(rs.getBoolean("fault_deal")==true){
					fault.setFault_reply(rs.getString("serve_des"));
				}
			}
			return fault;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	
	/**
	 * 获取所有故障信息
	 * @return
	 */
	public ArrayList<Fault> getAllFault() {
		DBConnect dbc = new DBConnect();
		ArrayList<Fault> faultlist = new ArrayList<Fault>();
		String sql = "{call getAllFault()}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Fault fault = new Fault();
				fault.setDorm_name(rs.getString("dorm_name"));
				fault.setFault_id(rs.getInt("fault_id"));
				fault.setStu_name(rs.getString("stu_name"));
				fault.setFault_des(rs.getString("fault_des"));
				fault.setFault_deal(rs.getBoolean("fault_deal"));
				String str = rs.getString("fault_time");
				if(str != null){
					fault.setFault_time(str.substring(0, 16));
				}
				fault.setFault_type(rs.getString("fault_type"));
				fault.setStu_tel(rs.getString("stu_tel"));
				fault.setBuilding_name(rs.getString("building_name"));
				fault.setFault_reply("无");
				fault.setServe_time("");
				if(rs.getBoolean("fault_deal")==true){
					fault.setFault_reply(rs.getString("serve_des"));
					fault.setServe_time(rs.getString("serve_time").substring(0, 16));
				}
				faultlist.add(fault);
			}
			return faultlist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}

	/**
	 * 根据管理员id及故障类型flag获取该管理员管理寝室楼的所有故障信息
	 * @param admin_id
	 * @param flag
	 * @return
	 */
	public ArrayList<Fault> getAdminFault(int admin_id, boolean flag) {
		DBConnect dbc = new DBConnect();
		ArrayList<Fault> faultlist = new ArrayList<Fault>();
		String sql = "{call getAdminFault(?,?)}";
		try {
			CallableStatement stmt = dbc.getConnection().prepareCall(sql);
			stmt.setInt(1, admin_id);
			stmt.setBoolean(2, flag);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Fault fault = new Fault();
				fault.setDorm_name(rs.getString("dorm_name"));
				fault.setFault_id(rs.getInt("fault_id"));
				fault.setStu_name(rs.getString("stu_name"));
				fault.setFault_des(rs.getString("fault_des"));
				fault.setFault_deal(rs.getBoolean("fault_deal"));
				String str = rs.getString("fault_time");
				if(str != null){
					fault.setFault_time(str.substring(0, 16));
				}
				fault.setFault_type(rs.getString("fault_type"));
				fault.setStu_tel(rs.getString("stu_tel"));
				fault.setFault_reply("无");
				fault.setFault_reply_admin("");
				fault.setServe_time("");
				if(flag==true){
					fault.setFault_reply(rs.getString("serve_des"));
					fault.setFault_reply_admin(rs.getString("admin_name"));
					fault.setServe_time(rs.getString("serve_time").substring(0, 16));
				}
				faultlist.add(fault);
			}
			return faultlist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
}
